package com.fzrskj.cloud.free.user.dao;

import com.fzrskj.cloud.free.core.utils.AgentDataRangeUtils;
import com.fzrskj.cloud.free.core.utils.CloudContextUtils;
import com.fzrskj.cloud.free.core.utils.CloudDbDrvUtils;
import com.fzrskj.cloud.free.core.utils.DataRangeUtils;
import com.fzrskj.cloud.free.user.bean.UserIssue;
import com.fzrskj.cloud.free.user.bean.UserIssueView;
import ms.core.consts.SysErr;
import ms.core.db.IDbDrv;
import ms.core.object.CObject;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class UserIssueDao extends CObject {
    //private declarations
    private IDbDrv db = null;

    //constructor function to initialize database access object
    public UserIssueDao() {
        db = CloudDbDrvUtils.getDbUtilsDrv();
    }

    //to free resource
    @Override
    public void dispose() {
        db.closeConnection();
    }

    /**
     * 根据条件查询工单
     *
     * @param cdt
     * @param page
     * @param pagesize
     * @return
     */
    public List<UserIssueView> queryIssue(Map<String, Object> cdt, int page, int pagesize) {
        //if (!cdt.containsKey("agentId")) {
            cdt.put("agentId", CloudContextUtils.getAgentId());
        //}
        StringBuilder sb = new StringBuilder("select ui.*,");
        sb.append("(select t.description from dic_paramindex n,dic_paramitem t where t.paramId=n.paramEName  and n.paramEName='workOrderStatus' and t.value= ui.state) as statusName ");
        sb.append("from user_issue ui where parentIssueId=0 ");
        List<Object> list = new ArrayList<>();
        for (Map.Entry<String, Object> c : cdt.entrySet()) {
            if (c.getKey().equals("issueCode")) {
                String[] arr = c.getValue().toString().split(",");
                for (int i = 0; i < arr.length; i++) {
                    if (i == 0) {
                        sb.append(" and issueCode=?");
                        list.add(arr[i]);
                    } else {
                        sb.append(" or issueCode=?");
                        list.add(arr[i]);
                    }
                }
            } else if (c.getKey().equals("start")) {
                sb.append(" and issueTime >?");
                list.add(c.getValue());
            } else if (c.getKey().equals("end")) {
                sb.append(" and issueTime <=?");
                list.add(c.getValue());
            } else {
                sb.append(" and " + c.getKey() + "=?");
                list.add(c.getValue());
            }
        }

        //数据隔离
        DataRangeUtils.formatRangeSql(sb, " ui.userId ", list);

        sb.append(" order by issueTime desc");
        if ((page > 0) && (pagesize > 0)) {
            sb.append(String.format(" limit %d,%d", (page - 1) * pagesize, pagesize));
        }
        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }
        List<UserIssueView> queryIssue = db.queryForBeans(sb.toString(), UserIssueView.class, params);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return queryIssue;
    }

    /**
     * 根据条件查询所找到的记录总数
     *
     * @param cdt
     * @return
     */
    public int queryIssueCount(Map<String, Object> cdt) {
        StringBuilder sb = new StringBuilder("select count(id) from user_issue where parentIssueId=0 ");
        List<Object> list = new ArrayList<Object>();
        for (Map.Entry<String, Object> c : cdt.entrySet()) {
            if (c.getKey().equals("issueCode")) {
                String[] arr = c.getValue().toString().split(",");
                for (int i = 0; i < arr.length; i++) {
                    if (i == 0) {
                        sb.append(" and issueCode=?");
                        list.add(arr[i]);
                    } else {
                        sb.append(" or issueCode=?");
                        list.add(arr[i]);
                    }
                }
            } else if (c.getKey().equals("start")) {
                sb.append(" and issueTime >?");
                list.add(c.getValue());
            } else if (c.getKey().equals("end")) {
                sb.append(" and issueTime <=?");
                list.add(c.getValue());
            } else {
                sb.append(" and " + c.getKey() + "=?");
                list.add(c.getValue());
            }
        }

        //代理数据隔离
        AgentDataRangeUtils.formatDataRange(CloudContextUtils.getAgentId(), sb);

        //数据隔离
        DataRangeUtils.formatRangeSql(sb, " userId ", list);

        Object[] params = new Object[list.size()];
        for (int i = 0; i < list.size(); i++) {
            params[i] = list.get(i);
        }
        long queryIssueCounts = (Long) db.queryFieldValue(sb.toString(), Long.class, params, 1);
        int queryIssueCount = (int) queryIssueCounts;
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return queryIssueCount;
    }

    /**
     * 根据userName与id删除对应的记录
     *
     * @param userName
     * @param id
     * @return
     */
    public int delIssue(String userName, int id) {
        String sql = "delete from user_issue where userName=? and id=? or parentIssueId=? and agentId=? ";
        int ret = db.executeSql(sql, new Object[]{userName, id, id, CloudContextUtils.getAgentId()});
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    /**
     * 根据id与userName查询详细信息
     *
     * @param userName
     * @param id
     * @return
     */
    public UserIssue queryDetails(String userName, int id) {
        StringBuffer sb = new StringBuffer("select ui.* ");
        sb.append("from user_issue ui where parentIssueId=0 and userName=? and id=? and agentId=? ");
        UserIssue queryDetails = db.queryForBean(sb.toString(), UserIssue.class, new Object[]{userName, id, CloudContextUtils.getAgentId()});
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return queryDetails;
    }

    /**
     * 根据id与userName查询详细信息
     *
     * @param userName
     * @param id
     * @return
     */
    public UserIssueView queryDetailsView(String userName, int id) {
        StringBuffer sb = new StringBuffer("select ui.*,");
        sb.append("(select t.description from dic_paramindex n,dic_paramitem t where t.paramId=n.paramEName  and n.paramEName='workOrderStatus' and t.value= ui.state) as statusName ");
        sb.append("from user_issue ui where parentIssueId=0 and userName=? and id=? and agentId=? ");
        UserIssueView queryDetails = db.queryForBean(sb.toString(), UserIssueView.class, new Object[]{userName, id, CloudContextUtils.getAgentId()});
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return queryDetails;
    }

    /**
     * 根据userName与id查询回复内容
     *
     * @param userName
     * @param id
     * @return
     */
    public List<UserIssue> queryContent(String userName, int id, int page, int pagesize) {
        String sql = "select * from user_issue where parentIssueId=? and userName=? and agentId=? ";
        if (page > 0 && pagesize > 0) {
            sql += " order by issueTime desc";
            sql += " limit " + (page - 1) * pagesize + "," + pagesize;
        }
        List<UserIssue> queryContent = db.queryForBeans(sql, UserIssue.class, new Object[]{id, userName, CloudContextUtils.getAgentId()});
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return queryContent;
    }

    /**
     * 查询回复内容总数
     *
     * @param userName
     * @param id
     * @return
     */
    public int queryCount(String userName, int id) {
        String sql = "select count(id) from user_issue where parentIssueId=? and userName=? and agentId=?";
        long queryCounts = (Long) db.queryFieldValue(sql, Long.class, new Object[]{id, userName, CloudContextUtils.getAgentId()}, 1);
        int queryCount = (int) queryCounts;
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return queryCount;
    }

    /**
     * 根据对象插入数据
     *
     * @param obj
     * @return
     */
    public int replyIssue(UserIssue obj) {
        //if (obj.getAgentId() == null){
            obj.setAgentId(CloudContextUtils.getAgentId());
       // }
        String sql = "insert into user_issue(attachmentUrl,email,businessName,issueCode,issueDesc," +
                "issueName,issueTime,identId,identName,mobile," +
                "notifyType,parentIssueId,replyTime,replyer,replyerId," +
                "state,userId,userName,workId,secretInfo,agentId)" +
                "values(?,?,?,?,?," +
                "?,?,?,?,?," +
                "?,?,?,?,?," +
                "?,?,?,?,?,?);";
        Object[] prm = {obj.getAttachmentUrl(), obj.getEmail(), obj.getBusinessName(), obj.getIssueCode(), obj.getIssueDesc(),
                obj.getIssueName(), obj.getIssueTime(), obj.getIdentId(), obj.getIdentName(), obj.getMobile(), obj.getNotifyType(),
                obj.getParentIssueId(), obj.getReplyTime(), obj.getReplyer(), obj.getReplyerId(), obj.getState(), obj.getUserId(),
                obj.getUserName(), obj.getWorkId(), obj.getSecretInfo(), obj.getAgentId()};
        int ret = db.executeSql(sql, prm);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    /**
     * 根据id更新属性
     *
     * @param id
     * @return
     */
    public int updateIssue(int id, int state) {
        String sql = "update user_issue set state=? where id=? and agentId=?";
        int ret = db.executeSql(sql, new Object[]{state, id, CloudContextUtils.getAgentId()});
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    /**
     * 查询最后回复问题
     *
     * @param parentIssueId
     * @param userId
     * @return
     */
    public UserIssue queryLastIssue(long parentIssueId, int userId) {
        String sql = "select * from user_issue where parentIssueId=? or id=? and userId=? and agentId=? order by issueTime desc limit 1";
        UserIssue queryDetails = db.queryForBean(sql, UserIssue.class, new Object[]{parentIssueId, parentIssueId, userId, CloudContextUtils.getAgentId()});
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return queryDetails;
    }

    /**
     * 查询过时工单
     *
     * @param hour
     * @return
     */
    public List<UserIssue> queryObsoleteIssue(int hour) {
        StringBuffer sb = new StringBuffer("select * from user_issue a,(select workid,max(id) as id from user_issue ");
        sb.append("where TIMESTAMPDIFF(hour,issuetime,now())>? and state=4 group by workid) as b where a.id=b.id ");
        sb.append("and a.parentIssueId not in(select id from user_issue where state=3)");
        List<UserIssue> list = db.queryForBeans(sb.toString(), UserIssue.class, new Object[]{hour});
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return list;
    }

    /**
     * 查询未完成工单
     *
     * @param issueCode
     * @param identName
     * @param userId
     * @return
     */
    public UserIssue queryNoEndUserIssue(String issueCode, String identName, int userId) {
        String sql = "select * from user_issue where issueCode=? and identName=? and userId=? and agentId=? and state!=3 and parentIssueId=0 order by id desc limit 1";
        UserIssue queryDetails = db.queryForBean(sql, UserIssue.class, new Object[]{issueCode, identName, userId, CloudContextUtils.getAgentId()});
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return queryDetails;
    }
}